package net.yxsoft.kit;

import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import net.yxsoft.enumclass.LockEnumCls;
import net.yxsoft.model.SSynDetails;
import net.yxsoft.model.formdesigner.FormField;
import net.yxsoft.model.infrastructure.TsCode;
import net.yxsoft.model.infrastructure.TsDatamodelProperties;
import net.yxsoft.model.infrastructure.TsParty;
import net.yxsoft.service.AreaService;
import net.yxsoft.service.infrastructure.CodeService;
import rapid.core.Authuser;
import rapid.core.ContextStateEnum;
import rapid.core.TResult;
import rapid.util.StrKit;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static java.util.regex.Pattern.compile;

/**
 * Created by wj on 2020/12/15.
 */
public class WebUploaderKitDL {


    public static TResult powerfulImport(String fileName, List<Map<Integer, String>> impData, String partName, String udmKey, String rulr, List<FormField> formFields, Authuser user, String rid) {

        TResult _result = new TResult();

        _result = sqlFactory(fileName,impData, partName, udmKey, rulr, formFields, user, rid);
        return _result;
    }


    public static TResult powerfulImportByEim(String flieName, List<Map<Integer, String>> impData, String partName, String udmKey, String rulr, List<FormField> formFields, Authuser user, String rid) {

        TResult _result = new TResult();

        _result = sqlFactory(flieName,impData, partName, udmKey, rulr, formFields, user, rid);
        return _result;
    }




    private static TResult sqlFactory(String flieName, List<Map<Integer, String>> impData, String partName, String udmKeyNames, String rulr, List<FormField> formFields, Authuser user, String rid) {








        if(flieName!=null&&flieName!=""){
            flieName=flieName.substring(7,flieName.length()-4);
        }

        TResult _result = new TResult();
        StringBuilder _strFailMessage = new StringBuilder();
        StringBuilder keyswhere = new StringBuilder();
        StringBuilder keyscol = new StringBuilder();
        StringBuilder keysfz = new StringBuilder();
        StringBuilder leftnull = new StringBuilder();
        StringBuilder indexcol = new StringBuilder();
        StringBuilder indexname = new StringBuilder();
        String partNameImportTemp=partName+"_import_temp";
        boolean _isDynamicForm = false;
        List<String> _exSql = new ArrayList<String>();
        List<Record> dbdata ;
        Long sjc= ChineseDateUtil.getNextNaos();
        //已启用的字段
        List<TsDatamodelProperties> _cols = new ArrayList<TsDatamodelProperties>();
        List<SSynDetails> _synonyms =  SSynDetails.dao.find("select * from s_syn_details");
        String _formid = "";
        //表名是FN_FORM_DATA说明是动态表单导入
        String _formid_part = "";
        if("FN_FORM_DATA".equalsIgnoreCase(partName)){
            _isDynamicForm = true;
            //动态表单导入
            _formid = formFields.get(0).getFormId();

            for (FormField _field : formFields) {
                TsDatamodelProperties _c = new TsDatamodelProperties();
                _c.setAttribName(_field.getField());
                _c.setAttribDescn(_field.getTitle());
                _c.setAttribType(_field.getName().split("\\|")[1]);
                _c.setCodeType(_field.getCodeType());
                _c.setCodeCustomid(_field.getCodeType());
                _c.setCodeLeaf(_field.getCodeLeaf());
                _c.setCodeName(_field.getCodeName());
                _cols.add(_c);
            }


        }else{
            _formid_part = formFields.get(0).getFormId();



            if (StrKit.notBlank(partName) && partName.equals("TS_USER")) {
                _cols = new ArrayList<TsDatamodelProperties>();
                TsDatamodelProperties ts = new TsDatamodelProperties();
                ts.setAttribName("id");

            } else {
                TsParty _partyModel = TsParty.dao.findFirst("SELECT * FROM TS_PARTY WHERE NAME=?", partName);

                if (_partyModel == null) {
                    return _result;
                }
                _cols = TsDatamodelProperties.dao.find("SELECT * FROM TS_DATAMODEL_PROPERTIES WHERE MODEL_ID=?  AND ATTRIB_DESCN<>'''' AND STATE=?", _partyModel.getConflictId(), LockEnumCls.LOCKED.getValue());

            }

            CustomDb.updateWithExceptionHandling("CREATE  TABLE IF NOT EXISTS "+partNameImportTemp+" (LIKE "+partName+")");

        }

        if(_cols == null || _cols.size() == 0) {
            return _result;
        }


        //获取全部业务代码
        Map<Integer,List<Record>> tsCodeMap=new HashMap<>(); //缓存数据字典的父级list
        //主键组合的名称
        String[] _keysNames = udmKeyNames.split("\\|");
        List<String> _KeysNameslist = new ArrayList<String>();
        int is=1;

        for(String _kn : _keysNames) {

            keyswhere.append("t.").append(_kn).append("=").append("t1.").append(_kn);

            keyscol.append("a.").append(_kn);
            keysfz.append("t.").append(_kn);
            leftnull.append("t1.").append(_kn).append(" is null ");
            indexcol.append("`" + _kn + "`(1)");
            indexname.append(_kn );
            if(_keysNames.length>1 && is!=_keysNames.length){
                keyswhere.append(" and ");
                leftnull.append(" and ");
                keyscol.append(",");
                keysfz.append(",");
                indexcol.append(",");
                indexname.append("_");
            }
            _KeysNameslist.add(_kn);
            is++;
        }

        Integer _colsSize = impData.get(0).size();
        if(_isDynamicForm){
            _colsSize++;
        }
        String[] _headrow = new String[_colsSize]; //表头
        String[] _colsType = new String[_colsSize];  //字段类型
        String[] _colBusCode = new String[_colsSize]; //数据字典业务code
        int _counter = 0;
        int _keycounter = 0; //在表头从中文转换成数据库字段名时顺便验证主键组合在excel里是否全部存在


        for(Map<Integer, String> map : impData) { // 遍历取出的数据
            _counter++;
            if(map.size() == 0) {
                continue;
            }
            Set<Map.Entry<Integer, String>> _row = map.entrySet();
            if(_counter == 1) {
                for(Map.Entry<Integer, String> _r : _row) { //行数据 循环取每一列
                    Integer _k = _r.getKey();
                    String _V = _r.getValue();
                    //将excel表头中文转换成数据库字段名
                    boolean _stop = false;
                    String attribName="";

                    for (TsDatamodelProperties _m : _cols) { //数据库已锁定字段
                        if (_stop) {
                            break;
                        }

                        if (_V.trim().equals(_m.getAttribDescn().trim())) {
                            //   String _attrName = _m.getAttribName().trim();
                            attribName = _m.getAttribName().trim();
                            //   _headrow[_counter1] = _attrName;
                            //在表头从中文转换成数据库字段名时顺便验证主键组合在excel里是否全部存在,最后比对_keycounter的数字和主键集合_KeysNameslist的size；
                            //如果数字不相等，视为excel里不包含选择主键组合的全部主键
                            for (String _s : _KeysNameslist) {
                                if (_s.equals(attribName)) {
                                    _keycounter++;
                                    break;
                                }
                            }

                            _colsType[_k] = _m.getAttribType().trim().toLowerCase();

                            //    String _codeName = _m.getCodeName();
                            if (StrKit.notBlank(_m.getCodeLeaf()) && StrKit.notBlank(_m.getCodeType())) {
                                _colBusCode[_k] = _m.getCodeType() + "|" + _m.getCodeLeaf();
                                if ("PROVINCE".equals(_m.getCodeType()) || "CITY".equals(_m.getCodeType()) || "DISTRICT".equals(_m.getCodeType())) {
                                    String _lcode = "1";

                                    if ("CITY".equals(_m.getCodeType())) {
                                        _lcode = "2";
                                    }

                                    if ("DISTRICT".equals(_m.getCodeType())) {
                                        _lcode = "3";
                                    }

                                    tsCodeMap.put(_k, AreaService.INSTANCE.getAreaByLevelcode(_lcode));
                                } else {
                                    tsCodeMap.put(_k, CodeService.INSTANCE.getTsCodeByCodeTypeAndLeaf("BUSINESS", _m.getCodeType(), _m.getCodeLeaf()));
                                }
                            } else {
                                _colBusCode[_k] = "";
                            }

                            //       _counter1++;
                            _stop = true;
                        }
                        _headrow[_k] = attribName;
                    }
                    //region---------------------------------------------------同意词-------------------------------------------------------
                    if (StrKit.isBlank(_headrow[_k]) && _synonyms != null && _synonyms.size() > 0) {
                        //说明当前字段都没匹配到，需要走同意词匹配
                        boolean _hassyn = false;
                        String _synMainName = "";
                        for (SSynDetails _sd : _synonyms) {
                            if (_V.trim().equals(_sd.getAttrib01().trim())) {
                                _hassyn = true;
                                _synMainName = _sd.getAttrib02();
                                break;
                            } else if (_V.trim().equals(_sd.getAttrib02().trim())) {
                                _hassyn = true;
                                _synMainName = _sd.getAttrib02();
                                break;
                            } else {
                                continue;
                            }
                        }

                        //如果有同意词
                        if (_hassyn) {
                            _stop = false;

                            for (TsDatamodelProperties _m : _cols) { //数据库已锁定字段
                                if (_stop) {
                                    break;
                                }

                                //先跟同意词主词对比
                                if (_m.getAttribDescn().trim().equals(_synMainName)) {
                                    attribName = _m.getAttribName().trim();
                                } else {
                                    //主词不匹配再跟同意词字表对比
                                    for (SSynDetails _sd : _synonyms) {
                                        if (_m.getAttribDescn().trim().equals(_sd.getAttrib01().trim()) && _synMainName.equals(_sd.getAttrib02().trim())) {
                                            attribName = _m.getAttribName().trim();
                                            break;
                                        }
                                    }
                                }

                                //在表头从中文转换成数据库字段名时顺便验证主键组合在excel里是否全部存在,最后比对_keycounter的数字和主键集合_KeysNameslist的size；
                                //如果数字不相等，视为excel里不包含选择主键组合的全部主键
                                for (String _s : _KeysNameslist) {
                                    if (_s.equals(attribName)) {
                                        _keycounter++;
                                        break;
                                    }
                                }

                                _colsType[_k] = _m.getAttribType().trim();

                                if (StrKit.notBlank(_m.getCodeLeaf()) && StrKit.notBlank(_m.getCodeType())) {
                                    _colBusCode[_k] = _m.getCodeType() + "|" + _m.getCodeLeaf();
                                    if ("PROVINCE".equals(_m.getCodeType()) || "CITY".equals(_m.getCodeType()) || "DISTRICT".equals(_m.getCodeType())) {
                                        String _lcode = "1";

                                        if ("CITY".equals(_m.getCodeType())) {
                                            _lcode = "2";
                                        }

                                        if ("DISTRICT".equals(_m.getCodeType())) {
                                            _lcode = "3";
                                        }

                                        tsCodeMap.put(_k, AreaService.INSTANCE.getAreaByLevelcode(_lcode));
                                    } else {
                                        tsCodeMap.put(_k, CodeService.INSTANCE.getTsCodeByCodeTypeAndLeaf("BUSINESS", _m.getCodeType(), _m.getCodeLeaf()));
                                    }
                                } else {
                                    _colBusCode[_k] = "";
                                }

                                if (StrKit.notBlank(attribName)) {
                                    _stop = true;
                                }


                                _headrow[_k] = attribName;
                            }
                        }
                    }
                    //endregion---------------------------------------------------同意词----END---------------------------------------------------
                }
                //endregion
            } else {
                if(_isDynamicForm) {
                    _headrow[_colsSize - 1] = "ATTRIB_01";
                    _colsType[_colsSize - 1] = "VARCHAR2(100)".toLowerCase();

                }
                //主键组合在EXCEL里不存在，直接返回fail
                if(_KeysNameslist.size() != _keycounter) {
                    _result.setState(ContextStateEnum.FAIL);
                    _result.setMessage("选择的主键组合与EXCEL里的字段不符。");

                    return _result;
                }
                Map cs = new HashMap();
                cs.put("_row",_row);
                cs.put("_headrow",_headrow);
                cs.put("_colsType",_colsType);
                cs.put("_colBusCode",_colBusCode);
                cs.put("_KeysNameslist",_KeysNameslist);
                cs.put("partName",partNameImportTemp);
                cs.put("rulr",rulr);

                cs.put("tsCodeMap",tsCodeMap);
                cs.put("_exSql",_exSql);
                cs.put("_formid",_formid);
                cs.put("_formid_part",_formid_part);

                cs.put("sjc",sjc);
                cs.put("user",user);
                TResult _r = createSql(cs);
                if (_r.getState() == ContextStateEnum.FAIL) {
                    _strFailMessage.append("EXCEL第" + (_counter - 1) + "行：" + _r.getData());
                    String attrib_06="第" + (_counter - 1) + "行"+_r.getData();
                    String attrib_06_=attrib_06.substring(0,attrib_06.indexOf("列")+1);
                    String attrib_07=attrib_06.substring(attrib_06.indexOf("列")+1,attrib_06.length());
                    Date date=new Date();
                    SimpleDateFormat sd=new SimpleDateFormat("yyyy-MM-dd");
                    String sddata=sd.format(date);
                    String id = Helper.bulidUUID();
                    Record implogcode = Db.findFirst("select * from fn_form where CODE=?", PropKit.get("implogcode"));
                    if(implogcode!=null && StrKit.notBlank(implogcode.getStr("id")))
                        CustomDb.updateWithExceptionHandling("insert into fn_form_data (attrib_01,  attrib_03, attrib_04, attrib_06,attrib_07,conflict_id,  created,created_by," +
                                " created_by_name, last_upd,row_id)  values (?,?,?,?,?,?,?,?,?,?,?) ",implogcode.get("id"),partName,flieName,attrib_06_,attrib_07,id,sddata,user.getId(),user.getName(),sddata,id);

                }
            }
        }

        boolean isinset = false;

        boolean isgb2 = false;
        if(StrKit.notBlank(_formid)){
            indexcol.append(",`attrib_01`(1)");
            indexname.append("_attrib_01");
        }else if(StrKit.notBlank(_formid_part)){

            indexcol.append(",`form_id`(1)");
            indexname.append("_form_id");
        }

        CustomDb.updateWithExceptionHandling("alter table " + partNameImportTemp + " add index index_" + sjc + "(" + indexcol + ")");
        Record indexobj = Db.findFirst("SELECT a.TABLE_NAME,a.INDEX_name FROM information_schema.statistics a where a.TABLE_NAME=? and a.INDEX_name=?", partName, "index_" + indexname);
        if (indexobj == null) {
            CustomDb.updateWithExceptionHandling("alter  table " + partName + " add index index_" + indexname + "(" + indexcol + ")");
        }

        try {
            int[] i = Db.batch(_exSql, 1000);
           // if (StrKit.notBlank(_formid) ) {
                if (i.length > 0) {
                    impSJgb(sjc, keyscol.toString(), keysfz.toString(), _cols, partNameImportTemp, true, _formid,_formid_part,rid);

                    isinset = impInsert(sjc, leftnull.toString(), keyswhere.toString(), partName, partNameImportTemp, _formid,_formid_part,rid);

                    if (isinset)
                        isgb2 = impSJgb(sjc, keyscol.toString(), keysfz.toString(), _cols, partName, false, _formid,_formid_part,rid);

                    if (_cols.size() > _keycounter) {
                        if (isgb2)
                            impUpdate(_cols, keyswhere.toString(), partName, partNameImportTemp, rulr, _keycounter, sjc, _formid,_formid_part,rid);


                    }


                }
         //   }

        }catch (Exception e){
            e.printStackTrace();

        }finally {
            CustomDb.updateWithExceptionHandling("drop index index_" + sjc + " on " + partNameImportTemp);
            WebUploaderKitDL.deletels(partNameImportTemp, sjc);

        }





        _result.setState(ContextStateEnum.SUCCESS);
        if(StrKit.isBlank(_strFailMessage.toString())) {
            _result.setMessage(StrKit.isBlank(_strFailMessage.toString()) ? "导入成功" : _strFailMessage.toString());
        }else{
            _result.setMessage(StrKit.isBlank(_strFailMessage.toString()) ? "导入成功,但导入过程中出现有部分数据错误请查看导入日志。" : _strFailMessage.toString());
        }
        return _result;
    }



    @Override
    public int hashCode() {
        return super.hashCode();
    }

    /**
     * 万能导入没有匹配到主键的数据插入
     * @param sjc 时间戳
     * @param leftnull 空值判断
     * @param keyswhere 主键匹配条件
     * @param tabname 表名
     * @param lstabname 临时表名
     */
       public static boolean impInsert(Long sjc, String leftnull, String keyswhere, String tabname, String lstabname, String fid,String _formid_part,String rid){

        String insertj="";
        if(StrKit.notBlank(fid)){
             insertj=" and t.attrib_01=t1.attrib_01  ";
        }

           if(StrKit.notBlank(_formid_part)){
               insertj=" and t.form_id=t1.form_id  ";
           }
        if(StrKit.notBlank(rid)){
               insertj=" and t.row_id=t1.row_id  ";
        }

           CustomDb.updateWithExceptionHandling("insert into "+tabname+" select * from  (select t.* from "+lstabname+" t left  join "+tabname+" t1 on "+keyswhere+""+insertj+" and t1.modification_num=0    where "+leftnull+" ) s where s.row_id=?", sjc);

if(StrKit.notBlank(rid)){
    CustomDb.updateWithExceptionHandling("update "+tabname+" set row_id=? where row_id=?",rid, sjc.toString());
}else{
    CustomDb.updateWithExceptionHandling("update "+tabname+" set row_id=conflict_id where row_id=?", sjc.toString());
}



    return true;

    }

    /**
     * 万能导入数据归并
     * @param sjc 时间戳
     * @param keyscol 主键列
     * @param keysfz
     * @param _cols 字段集合
     * @param tabname 表名
     * @param sflsb 是否临时表
     * @param formid 动态表单id;
     */
    public static boolean impSJgb(Long sjc, String keyscol,String keysfz, List<TsDatamodelProperties> _cols, String tabname, boolean sflsb, String formid,String _formid_part,String rid){

        String gbwhererid="";
        String  gbgrouprid="";
        String  gbfzrid="";
        String lssrt="";
        if(sflsb){
            lssrt=" and row_id="+sjc;
        }else{

            if(StrKit.notBlank(rid)){
                gbwhererid="and a.row_id='"+rid+"' ";
                gbgrouprid=",a.row_id ";
                gbfzrid=",t.row_id ";

            }
        }
        String gbwherefid="";
        String  gbgroupfid="";
        String  gbfzfid="";
        if(StrKit.notBlank(formid)){
             gbwherefid="and a.attrib_01='"+formid+"' ";
              gbgroupfid=",a.attrib_01 ";
              gbfzfid=",t.attrib_01 ";

        }

        if(StrKit.notBlank(_formid_part)){
            gbwherefid="and a.form_id='"+formid+"' ";
            gbgroupfid=",a.form_id ";
            gbfzfid=",t.form_id ";

        }

        String sql= "SELECT   CONCAT("+keyscol+""+gbgroupfid+""+gbgrouprid+")  as fzzj,a.* FROM "+tabname+" a" +
                " WHERE ("+keyscol+""+gbgroupfid+""+gbgrouprid+") IN (SELECT "+keysfz+""+gbfzfid+""+gbfzrid+" FROM "+tabname+" t" +
                " GROUP BY "+keysfz+""+gbfzfid+""+gbfzrid+" HAVING count(t.conflict_id) > 1) AND a.conflict_id NOT IN" +
                "(SELECT max(t.conflict_id) FROM "+tabname+" t GROUP BY "+keysfz+""+gbfzfid+""+gbfzrid+" HAVING  count(t.conflict_id) > 1)  " +
                " "+gbwherefid+""+gbwhererid+" and  a.modification_num='0' "+lssrt+" order by fzzj";

        List<Record> rlist = Db.find(sql);


        String sql2= "SELECT   CONCAT("+keyscol+""+gbgroupfid+""+gbgrouprid+")  as fzzj,a.conflict_id FROM "+tabname+" a" +
                " WHERE ("+keyscol+""+gbgroupfid+""+gbgrouprid+") IN (SELECT "+keysfz+""+gbfzfid+""+gbfzrid+" FROM "+tabname+" t" +
                " GROUP BY "+keysfz+""+gbfzfid+""+gbfzrid+" HAVING count(t.conflict_id) > 1) AND a.conflict_id  IN" +
                "(SELECT max(t.conflict_id) FROM "+tabname+" t GROUP BY "+keysfz+""+gbfzfid+""+gbfzrid+" HAVING  count(t.conflict_id) > 1)  " +
                " "+gbwherefid+""+gbwhererid+" and  a.modification_num='0' "+lssrt+" order by fzzj";
        List<Record> llist = Db.find(sql2);


        for (Record rs : llist) {
            /**
             * 重复主键数据分组
             */

            for (Record res : rlist) {
                if (rs.getStr("fzzj").equals(res.getStr("fzzj"))) {
                    res.remove("FZZJ");
                    if(sflsb){
                        Db.delete(tabname, "conflict_id", res);
                    }else {
                        res.set("MODIFICATION_NUM", -1);
                        res.set("PAR_ROW_ID", rs.get("ROW_ID"));
                        CustomDb.updateWithExceptionHandling(tabname, "conflict_id", res);
                    }
                }
            }


        }
        return true;
    }

    /**
     * 删除临时数据
     * @param sjc
     */
    public static void deletels(String temptablename, Long sjc){
        Db.delete("delete from "+temptablename+" where row_id=?", sjc.toString());
    }


    /**
     * 万能导入更新
     * @param _cols
     * @param keyswhere
     * @param tabname
     * @param lstabname
     * @param rulr
     */
    public static  boolean impUpdate(List<TsDatamodelProperties> _cols, String keyswhere, String tabname, String lstabname, String rulr, int _keycounter, long sjc, String fid,String _formid_part,String rid){


        String insertj="";
        if(StrKit.notBlank(fid)){
            insertj=" and t.attrib_01=t1.attrib_01 ";

        }
        if(StrKit.notBlank(_formid_part)){
            insertj=" and t.form_id=t1.form_id ";

        }


        if(StrKit.notBlank(rid)){
            insertj+=" and t.row_id=t1.row_id ";
        }
        StringBuilder updatecol = new StringBuilder();
        StringBuilder isupsetsql = new StringBuilder();
        updatecol.append("update ").append(tabname).append(" t1 ").append(",").append(lstabname).append("  t  set ");


        for(int ss=0;ss<_cols.size();ss++){
            TsDatamodelProperties ts=_cols.get(ss);
            if(keyswhere.toString().indexOf(ts.getAttribName())==-1){
                if(rulr.equals("h"))
                    updatecol.append("t1.").append(ts.getAttribName()).append("=").append("IFNULL(").append("t1.").append(ts.getAttribName()).append(",").append("t.").append(ts.getAttribName()).append(")");
                else
                    updatecol.append("t1.").append(ts.getAttribName()).append("=").append("IFNULL(").append("t.").append(ts.getAttribName()).append(",").append("t1.").append(ts.getAttribName()).append(")");

                updatecol.append(",");
            }
        }
        updatecol=new StringBuilder(updatecol.substring(0,updatecol.lastIndexOf(",")));
        updatecol.append(" where  t1.modification_num=0 and ").append(keyswhere).append(" and t.row_id='"+sjc+"' "+insertj+" " );

        isupsetsql.append("select count(*) cut from ").append(tabname).append(" t1 ").append(",").append(lstabname).append("  t   ");
        isupsetsql.append(" where  t1.modification_num=0 and ").append(keyswhere).append(" and t.row_id='"+sjc+"' "+insertj+" " );
         Long i= Db.findFirst(isupsetsql.toString()).getLong("cut");
        if(i>0) {
            CustomDb.updateWithExceptionHandling(updatecol.toString());
        }
        return true;
    }

    /**
     * 创建sql
     * @param cs
     * @return
     */
    private static TResult createSql(Map cs) {

        Set<Map.Entry<Integer, String>> rowData = (Set<Map.Entry<Integer, String>>)cs.get("_row");
        String[] head= (String[])cs.get("_headrow");
        String[] colType=(String[])cs.get("_colsType");
        String[] colBusCode=(String[])cs.get("_colBusCode");
        List<String> _keysNames=(List<String>)cs.get("_KeysNameslist");
        String tableName=cs.get("partName").toString();
        String rulr=cs.get("rulr").toString();


        Map<Integer,List<Record>> tsCodeMap=(Map<Integer,List<Record>>)cs.get("tsCodeMap");
        List<String> exSql=(List<String>) cs.get("_exSql");
        String formid=cs.get("_formid").toString();
        String _formid_part=cs.get("_formid_part").toString();
        String sjc=cs.get("sjc").toString();
        Authuser user=(Authuser)cs.get("user");
        TResult _result = new TResult();
        StringBuilder _strInsert = new StringBuilder();
        _strInsert.append("INSERT INTO " + tableName);
        StringBuilder _strInsertColnames = new StringBuilder();
        _strInsertColnames.append("(");
        StringBuilder _strInsertValues = new StringBuilder();
        _strInsertValues.append("(");
        StringBuilder _strUpdate = new StringBuilder();
        _strUpdate.append("UPDATE " + tableName + " SET ");
        StringBuilder _strUpdateAll = new StringBuilder();
        _strUpdateAll.append("UPDATE " + tableName + " SET ");
        StringBuilder _strWhere = new StringBuilder();
        _strWhere.append(" WHERE 1=1");
        StringBuilder _strExistdb = new StringBuilder();
        _strExistdb.append("SELECT * FROM " + tableName);
        Map<String, String> _keysKV = new HashMap<String, String>();
        boolean _hasfirst = false; //处理避免第一列字段没找到，下面index0的下标不执行
        int _index = 0;
        //遍历倒入excel数据的行数据的每一列
        for(Map.Entry<Integer, String> _cell : rowData) {
            Integer _k = _cell.getKey();
            _index =_k;
            String _V = _cell.getValue() == null ? "" : _cell.getValue().trim();
            String _sqlColName = head[_index];
            if(StrKit.isBlank(_sqlColName)){
                continue;
            }
            String _sqlColVal = "";
            String _coltype = " "+colType[_index].toLowerCase();
            //验证字段类型是否是日期或者数值型，如果是，那么验证其的合法性
            if(_coltype.indexOf("date")>0 || _coltype.indexOf("int")>0 || _coltype.indexOf("decimal")>0 ) {
                _V = _V.replace("/", "-").replace("：", ":").replace("年", "-").replace("月", "-").replace("日", "").replace("_", "-").replace("|", "-").replace("\\", "-");
                _result = validFieldType(_index,_V, colType[_index]);
                if (_result.getState() == ContextStateEnum.SUCCESS) {
                    if (_coltype.indexOf("date") > 0) {

                        if(_V.trim().equals(""))
                            _sqlColVal = "null";
                        else
                            _sqlColVal = "'" + _V + "'";

                    } else {
                        if (_coltype.indexOf("decimal") > 0 || _coltype.indexOf("int") > 0) {
                            if(_V.trim().equals(""))
                                _sqlColVal = "null";
                            else
                                _sqlColVal = "'" + _V + "'";
                        }else{
                            _sqlColVal = "" + _V + "";
                        }


                    }
                } else {
                    return _result;
                }
            } else {
                if (_V.trim().length() <= 0) {
                    _sqlColVal = "null";
                } else{
                    int length= _V.trim().getBytes().length;
                    if(_coltype.equals(" varchar(50)")&&_V.trim().getBytes().length > 50){
                        _result.setState(ContextStateEnum.FAIL);
                        _result.setData("第"+(_index+1)+"列数据长度超出限制");
                        return _result;
                    }else if(_coltype.equals(" varchar(100)")&&_V.trim().getBytes().length > 100){
                        _result.setState(ContextStateEnum.FAIL);
                        _result.setData("第"+(_index+1)+"列数据长度超出限制");
                        return _result;
                    }else if(_coltype.equals(" varchar(200)")&&_V.trim().getBytes().length > 200){
                        _result.setState(ContextStateEnum.FAIL);
                        _result.setData("第"+(_index+1)+"列数据长度超出限制");
                        return _result;
                    }else if(_coltype.equals(" varchar(400)")&&_V.trim().getBytes().length > 400){
                        _result.setState(ContextStateEnum.FAIL);
                        _result.setData("第"+(_index+1)+"列数据长度超出限制");
                        return _result;
                    }else if(_coltype.equals(" varchar(2000)")&&_V.trim().getBytes().length > 2000){
                        _result.setState(ContextStateEnum.FAIL);
                        _result.setData("第"+(_index+1)+"列数据长度超出限制");
                        return _result;
                    }else if(_coltype.equals(" char(1)")&&length>1){
                        _result.setState(ContextStateEnum.FAIL);
                        _result.setData("第"+(_index+1)+"列数据长度超出限制");
                        return _result;
                    }else {
                        _sqlColVal = "'" + _V.replaceAll("'", "’").replaceAll(",", "，") + "'";
                    }
                }
            }
            //不为空说明是关联代码项，提取业务代码

            if(StrKit.notBlank(colBusCode[_index])) {
                String _codeCustomId = associatedCode(_V, tsCodeMap.get(_index));
                _sqlColVal = "'" + _codeCustomId + "'";
            }
            if(!_hasfirst) {
                _hasfirst = true;
                String _key = Helper.bulidUUID();
                _strInsertColnames.append("CONFLICT_ID,");
                _strInsertColnames.append("ROW_ID,");
                _strInsertColnames.append("CREATED,");
                _strInsertColnames.append("CREATED_BY,");
                _strInsertColnames.append("LAST_UPD,");
                _strInsertColnames.append("LAST_UPD_BY,");
                _strInsertValues.append("'"+_key+"',");
              /*  if(StrKit.notBlank(formid))
                    _strInsertValues.append("'"+sjc+"',");
                else
                    _strInsertValues.append("'"+_key+"',");*/

                _strInsertValues.append("'"+sjc+"',");
                _strInsertValues.append("now(),");
                _strInsertValues.append("'"+user.getId()+"',");

                _strInsertValues.append("now(),");
                _strInsertValues.append("'"+user.getId()+"',");

                if(StrKit.notBlank(formid)){
                    _strInsertColnames.append("ATTRIB_01,ATTRIB_101,CREATED_BY_NAME,LAST_UPD_BY_NAME,");
                    _strInsertValues.append("'"+formid+"','-2','"+user.getName()+"','"+user.getName()+"',");

                    _strWhere.append(" AND ATTRIB_01 ='" + formid + "' ");
                    _strUpdate.append(" LAST_UPD_BY_NAME='"+user.getName()+"',");

                }
                if(StrKit.notBlank(_formid_part)){
                    _strInsertColnames.append("form_id,audit_state,CREATED_BY_NAME,LAST_UPD_BY_NAME,");
                    _strInsertValues.append("'"+_formid_part+"','-2','"+user.getName()+"','"+user.getName()+"',");

                    _strWhere.append(" AND form_id ='" + _formid_part + "' ");
                    _strUpdate.append(" LAST_UPD_BY_NAME='"+user.getName()+"',");

                }

                _strInsertColnames.append(_sqlColName);
                _strInsertValues.append(_sqlColVal);
                _strUpdate.append(" LAST_UPD_BY='"+user.getId()+"',");

                _strUpdate.append(" LAST_UPD=now(),");
                _strUpdateAll.append(" LAST_UPD_BY='"+user.getId()+"',");
                _strUpdateAll.append(" LAST_UPD=sysdate,");
                if(_V != null && !_V.equals("")) {
                    _strUpdate.append(" " + _sqlColName + "=" + _sqlColVal + " ");
                }
                _strUpdateAll.append(" " + _sqlColName + "=" + _sqlColVal + " ");
            } else {
                _strInsertColnames.append("," + _sqlColName);
                _strInsertValues.append("," + _sqlColVal + "");
                if(_V != null && !_V.equals("")) {
                    _strUpdate.append(" , " + _sqlColName + "=" + _sqlColVal + " ");
                }
                _strUpdateAll.append(" , " + _sqlColName + "=" + _sqlColVal + " ");
            }
            //属于主键
            if(_keysNames.contains(_sqlColName.trim())) {
                _strWhere.append(" AND " + _sqlColName + "=" + _sqlColVal + " ");
                _keysKV.put(_sqlColName, _V);
            }

        }
        _strInsertColnames.append(")");
        _strInsertValues.append(")");
        _strInsert.append(_strInsertColnames.toString()).append(" VALUES").append(_strInsertValues.toString());
        _strUpdate.append(_strWhere.toString());
        _strUpdateAll.append(_strWhere.toString());
        _strExistdb.append(_strWhere.toString());
        exSql.add(_strInsert.toString());

        _result.setState(ContextStateEnum.SUCCESS);
        return _result;
    }

    //获取数据字典中对应codename的custId
    public static String getTsCodeCustIdByCodeName(String rootCustId, String oneCustId, String leaf, String codeName) {
        TsCode tsCode = TsCode.dao.findFirst("select * from ts_code where rootcustomid=? and codetype=? and leaf=? and enabled='1' and codename=? ", rootCustId, oneCustId, leaf, codeName);
        if (null != tsCode && StrKit.notBlank(tsCode.getCustomid())) {
            return tsCode.getCustomid();
        }
        return "";
    }



    /**
     * 根据业务代码编号名称匹配缓存数据，返回业务代码自定义编号
     **/
    public static String associatedCode(String codeName, List<Record> businessCodes) {

        String _returnCustomId = "";
        if (businessCodes != null && businessCodes.size() > 0) {
            for (Record _c : businessCodes) {

                if ("code".equals(_c.getStr("tag"))) {
                    if (_c.get("CODENAME").equals(codeName)) {
                        _returnCustomId = _c.get("CUSTOMID");
                        break;
                    }
                } else {
                    if (_c.get("ATTRIB_02").equals(codeName) || _c.get("ATTRIB_03").equals(codeName)) {
                        _returnCustomId = _c.get("ATTRIB_01");
                        break;
                    }
                }


            }
        }
        return _returnCustomId;

    }

    /**
     * 验证字段类型
     **/
    public static TResult validFieldType(int _index, String val, String fieldType) {

        TResult _result = new TResult();

        String _fieldType = " " + fieldType.toLowerCase();

        if (_fieldType.indexOf("date") > 0) {
            if (validDateTime(val)||val.trim().equals("")) {
                _result.setState(ContextStateEnum.SUCCESS);
            } else {
                _result.setState(ContextStateEnum.FAIL);
                _result.setData("第"+(_index+1)+"列存在非法的日期格式数据。\n");
            }
        } else if (_fieldType.indexOf("int") > 0 || _fieldType.indexOf("decimal") > 0) {
            Pattern patternInt = compile("^[-\\+]?[\\d]*$");
            Pattern patternDouble = compile("^[-\\+]?[.\\d]*$");
            if (patternInt.matcher(val).matches() || patternDouble.matcher(val).matches()) {
                _result.setState(ContextStateEnum.SUCCESS);
            } else {
                _result.setState(ContextStateEnum.FAIL);
                _result.setData("第"+(_index+1)+"列存在非法的数值格式数据。\n");
            }
        } else {
            _result.setState(ContextStateEnum.FAIL);
            _result.setData("第"+(_index+1)+"列存在字段类型有误数据。\n");
        }

        return _result;
    }


    /**
     * 验证日期时间格式和合法性
     */
    public static boolean validDateTime(String strDate) {

        Pattern p = compile("^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))(\\s(((0?[0-9])|([1][0-9])|([2][0-4]))\\:([0-5]?[0-9])((\\s)|(\\:([0-5]?[0-9])))))?$");

        return p.matcher(strDate).matches();
    }

    /**
     * 验证年月日的合法性
     **/
    public static boolean validDate(String strDate) {
        //使用正则表达式 测试 字符 符合 yyyy-MM-dd,yyyy/MM/dd 的格式

        boolean _tag = true;

        String _date = "";

        if (strDate.trim().length() >= 10) {
            _date = strDate.substring(1, 10);
        } else {

            return false;
        }

        String[] array;

        Pattern p = compile("\\d{4}+[-]\\d{1,2}+[-]\\d{1,2}+");
        Matcher m = p.matcher(_date);
        if (!m.matches()) {

            Pattern p1 = compile("\\d{4}+[/]\\d{1,2}+[/]\\d{1,2}+");
            Matcher m1 = p.matcher(_date);
            _tag = m.matches();

            if (!_tag) {
                return false;
            }

            //得到年月日
            array = _date.split("\\/");

        } else {

            //得到年月日
            array = _date.split("\\-");
        }

        int year = Integer.valueOf(array[0]);
        int month = Integer.valueOf(array[1]);
        int day = Integer.valueOf(array[2]);

        if (month < 1 || month > 12) {
            return false;
        }
        int[] monthLengths = new int[]{0, 31, -1, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};
        if (isLeapYear(year)) {
            monthLengths[2] = 29;
        } else {
            monthLengths[2] = 28;
        }
        int monthLength = monthLengths[month];
        if (day < 1 || day > monthLength) {
            return false;
        }
        return true;
    }

    /**
     * 是否是闰年
     */
    private static boolean isLeapYear(int year) {
        return ((year % 4 == 0 && year % 100 != 0) || year % 400 == 0);
    }

    /**
     * 验证日期时间格式和合法性
     */
    public static boolean validNewDateTime(String strDate) {
        if (StrKit.isBlank(strDate)) {
            return false;
        }
        strDate = strDate.replace("/", "-").replace("：", ":").trim();
        if (strDate.length() > 10) {
            //时间
            DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            format.setLenient(false);
            try {
                format.parse(strDate);
            } catch (Exception e) {
                return false;
            }
            return true;
        } else {
            //日期
            return validNewDate(strDate);
        }
    }

    /**
     * 验证年月日的合法性
     **/
    public static boolean validNewDate(String strDate) {
        //使用正则表达式 测试 字符 符合 yyyy-MM-dd,yyyy/MM/dd 的格式

        boolean _tag = true;

        String _date = "";

        if (strDate.trim().length() >= 10) {
            _date = strDate.substring(0, 10);
        } else {

            return false;
        }

        String[] array;

        Pattern p = compile("\\d{4}+[-]\\d{1,2}+[-]\\d{1,2}+");
        Matcher m = p.matcher(_date);
        if (!m.matches()) {

            Pattern p1 = compile("\\d{4}+[/]\\d{1,2}+[/]\\d{1,2}+");
            Matcher m1 = p.matcher(_date);
            _tag = m.matches();

            if (!_tag) {
                return false;
            }

            //得到年月日
            array = _date.split("\\/");

        } else {

            //得到年月日
            array = _date.split("\\-");
        }

        int year = Integer.valueOf(array[0]);
        int month = Integer.valueOf(array[1]);
        int day = Integer.valueOf(array[2]);

        if (month < 1 || month > 12) {
            return false;
        }
        int[] monthLengths = new int[]{0, 31, -1, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};
        if (isLeapYear(year)) {
            monthLengths[2] = 29;
        } else {
            monthLengths[2] = 28;
        }
        int monthLength = monthLengths[month];
        if (day < 1 || day > monthLength) {
            return false;
        }
        return true;
    }
}
